import pymysql as sql
from flask import jsonify
import datetime

connect = sql.Connect(
    host='rm-bp131vjnd9b99vmuq2o.mysql.rds.aliyuncs.com',
    port=3306,
    user='lin_432718',
    passwd='Lin817818',
    db='rail_traffic',
    charset='utf8'
)
cursor = connect.cursor()

def selectThe(risk_id):
    sql = "select threshold_id, attribute, threshold.range, norm, threshold.level, method " \
          "FROM threshold WHERE risk_id = '" + risk_id + "'"
    # print(sql)

    cursor.execute(sql)

    attribute = ['threshold_id', 'attribute', 'range', 'norm', 'level', 'method']
    l = []
    for item in cursor.fetchall():
        dic = dict(map(lambda x, y: [x, y], attribute, item))
        l.append(dic)
    # print(l)
    return jsonify(l)

def onDel(threshold_id):
    sql = "DELETE FROM threshold WHERE threshold_id = '" + threshold_id + "'"
    # print(sql)
    try:
        cursor.execute(sql)
        connect.commit()
    except:
        return jsonify(0)
    else:
        return jsonify(1)

def modifyThr(threshold_id, range, norm, level, method):

    sql = "UPDATE threshold SET threshold.range = '" + range + "', norm = '" + norm +"', threshold.level = '" + level + \
          "', method = '" + method + "' WHERE threshold_id = '" + threshold_id + "'"
    print(sql)

    try:
        cursor.execute(sql)
        connect.commit()
    except:
        return jsonify(0)
    else:
        return jsonify(1)

def add(risk_id, attribute, range, norm, level, method):
    thr_id = getnewnumber()

    sql = "INSERT INTO threshold(threshold_id, threshold.range, norm, risk_id, method, attribute, threshold.level) VALUES('" + \
          thr_id + "', '" + range + "', '" + norm + "', '" + risk_id + "', '" + method + "', '" + attribute + \
          "', '" + level + "')"

    print(sql)

    try:
        cursor.execute(sql)
        connect.commit()
    except:
        return jsonify(0)
    else:
        return jsonify(1)

# 给风险项寻找最大编号
def getnewnumber():
    sql = "select max(threshold_id) from threshold"
    cursor.execute(sql)
    data = cursor.fetchone()
    # print(data)
    number = int(data[0]) + 1
    #  print(number)
    str = "%03d" % number
    # print(str)

    return str